﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CuaHangLaptop.BIZ;
using System.Windows.Forms;
using System.Drawing;

namespace CuaHangLaptop.DAL
{
    class Laptop_DAL
    {
        internal static int Get_Lasted_ID()
        {
            SqlConnection conn = Connection.Connection_String();
            int id = 0;
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select id from LapTop order by id desc", conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                id = rdr.GetInt32(0);
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return id;
        }

        // SELECT * FROM NHA SAN XUAT
        internal static List<Laptop_BIZ> GetAll_LapTop()
        {
            SqlConnection conn = Connection.Connection_String();
            List<Laptop_BIZ> result = new List<Laptop_BIZ>();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from LapTop", conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Laptop_BIZ dd = new Laptop_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5),
                                                   rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11) , rdr.GetDecimal(12));
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // LẤY NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        internal static Laptop_BIZ Get_LapTop_ByCode(string _malaptop)
        {
            Laptop_BIZ nhasx = new Laptop_BIZ();
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            string s = "";
            try
            {
                conn.Open();
                s = String.Format("select * from LapTop where MaLaptop='{0}'", _malaptop);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();
                if (rdr.HasRows)
                {
                    rdr.Read();
                    nhasx = new Laptop_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5),
                                                       rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetDecimal(12));
                }
            }
            catch
            {
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return nhasx;
        }

        // LẤY TẤT CẢ NHÀ SẢN XUẤT CÓ MANSX = NSX.MANSX
        internal static List<Laptop_BIZ> GetAll_LapTop_ByNSX(string _maNSX)
        {
            List<Laptop_BIZ> result = new List<Laptop_BIZ>();
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format(@"select * from LapTop where MaNSX='{0}'", _maNSX);
                SqlCommand cmd = new SqlCommand(s, conn);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Laptop_BIZ dd = new Laptop_BIZ(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5),
                                                   rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetDecimal(12)); ;
                    result.Add(dd);
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return result;
        }

        // DELETE
        internal static void Delete(Laptop_BIZ nsx)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format(@"delete LapTop where id={0}", nsx.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // UPDATE
        internal static void Update(Laptop_BIZ nsx)
        {
            SqlConnection conn = Connection.Connection_String();
            try
            {
                conn.Open();
                String s = String.Format(@"update LapTop set Malaptop = '{0}', MaNSX = '{1}', TenLaptop = '{2}',CPU = '{3}',RAM = '{4}',HDD = '{5}',VGA = '{6}',Monitor = '{7}',BaoHanh = '{8}',ThongTinKhac = '{9}', Hinh = '{10}', Gia ={11} where id={12}", nsx.MaLaptop, nsx.NhaSX.MaNSX, nsx.TenLaptop, nsx.Cpu, nsx.Ram, nsx.Hdd, nsx.Vga, nsx.Monitor, nsx.Baohanh, nsx.Thongtin, nsx.IMG, nsx.Gia, nsx.ID);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                conn.Close();
            }
        }

        // INSERT
        internal static int Insert(Laptop_BIZ nsx)
        {
            int newId = -1;
            SqlConnection conn = Connection.Connection_String();
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                String s = String.Format(@"insert into LapTop values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', {11})", nsx.MaLaptop, nsx.NhaSX.MaNSX, nsx.TenLaptop, nsx.Cpu, nsx.Ram, nsx.Hdd, nsx.Vga, nsx.Monitor, nsx.Baohanh, nsx.Thongtin, nsx.IMG, nsx.Gia);
                SqlCommand cmd = new SqlCommand(s, conn);

                if (cmd.ExecuteNonQuery() > 0)
                {
                    s = String.Format(@"select id from LapTop where MaLaptop='{0}'", nsx.MaLaptop);
                    cmd.CommandText = s;
                    rdr = cmd.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        rdr.Read();
                        newId = Int32.Parse(rdr["id"].ToString());
                    }
                }
            }
            finally
            {
                if (rdr != null) rdr.Close();
                conn.Close();
            }
            return newId;
        }

    }
}
